package sg.com.techstudio.cfoconnect.excel;

import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import jxl.format.CellFormat;
import jxl.write.Label;
import jxl.write.NumberFormat;
import jxl.write.WritableCellFormat;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

import org.springframework.web.servlet.view.document.AbstractJExcelView;

public abstract class BaseExcel extends AbstractJExcelView {
	protected static final int SHEET_DEFAULT_COLUMN_WIDTH = 15;
	protected static final Calendar LOCAL_CALENDAR = Calendar.getInstance();
	protected static final int TIMEZONE_OFFSET = LOCAL_CALENDAR.get(Calendar.ZONE_OFFSET)+LOCAL_CALENDAR.get(Calendar.DST_OFFSET);
	
	protected static final SimpleDateFormat FILENAME_SUFFIX_SDF = new SimpleDateFormat("yyyyMMddhhmmss");
	protected static final String EXCEL_EXTENSION = ".xls";

	protected WritableSheet prepareWritableSheet(WritableWorkbook wb) throws WriteException {
		return prepareWritableSheet(wb, getTabname(), 0);
	}
	
	protected WritableSheet prepareWritableSheet(WritableWorkbook wb, String tabname, int i) throws WriteException {
		WritableSheet ws = wb.createSheet(tabname, i);
        ws.getSettings().setDefaultColumnWidth(getDefaultColumnWidth());
        return ws;
	}
	
	protected void processServletRequestResponse(HttpServletRequest req
			, HttpServletResponse resp) {
		resp.setContentType("application/octet-stream");
        resp.setHeader("Content-Disposition","attachment;filename="+getFilename());
	}
	
	protected abstract String getFilename();
	protected abstract String getTabname();
	
	protected int getDefaultColumnWidth() {
		return SHEET_DEFAULT_COLUMN_WIDTH;
	}
	
	protected WritableSheet writeColumnHeader(WritableSheet ws, int r, int c, String[] labelArr) throws RowsExceededException, WriteException {
		int offset=0;
		for(String s: labelArr) {
			ws.addCell(new Label(c+offset, r, s));
			offset++;
		}
		return ws;
	}
	
	protected String yesNo(boolean b) {
		return b?"YES":"NO";
	}
	
	protected Date gmtDateTime(Date localDate) {
		localDate.setTime(localDate.getTime()+TIMEZONE_OFFSET);
		return localDate;
	}
	
	protected CellFormat dollarFormat() {
		NumberFormat dollarCurrency = new NumberFormat(NumberFormat.CURRENCY_DOLLAR + " #,##0.00", NumberFormat.COMPLEX_FORMAT);
		return new WritableCellFormat(dollarCurrency);
	}
}
